Export data from Polis DB

pPolis2023-06-04

Exported data from Polis DB

I want to get my own past voting data from pol.is and do a lot of things with it.

We talked about whether we could get the data out if we set up an instance, and I told him that we had done so far as to set it up and collect the votes.

Data can be exported from the admin page for a conversation.

I don't see that on the menu...

I guess that means it's off in pol.is.

I wonder if I should pg_dump it.

code::

$ psql

Command 'psql' not found, but can be installed with:

$ sudo apt install postgresql-client-common

$ psql

Warning: No existing cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.

Error: You must install at least one postgresql-client-<version> package

code::

$ sudo apt update

$ sudo apt install postgresql-client

$ psql

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory

Is the server running locally and accepting connections on that socket?

code::

$ docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

12be7e86bdf7 compdem/polis-nginx-proxy:dev "/docker-entrypoint.…" 11 days ago Up 8 minutes 0.0.0.0:80->80/tcp, :::80->80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp polis-dev-nginx-proxy-1

e605f36eaf7d compdem/polis-server:dev "docker-entrypoint.s…" 11 days ago Up 8 minutes 0.0.0.0:5000->5000/tcp, :::5000->5000/tcp, 0.0.0.0:9229->9229/tcp, :::9229->9229/tcp polis-dev-server-1

25846362cfd3 compdem/polis-math:dev "entrypoint ./bin/run" 12 days ago Up 8 minutes 0.0.0.0:18975->18975/tcp, :::18975->18975/tcp polis-dev-math-1

20b437d2786c maildev/maildev:1.1.1 "/home/node/bin/mail…" 12 days ago Up 8 minutes (healthy) 0.0.0.0:1025->1025/tcp, :::1025->1025/tcp, 0.0.0.0:1080->1080/tcp, :::1080->1080/tcp polis-dev-maildev-1

f8d749409bda compdem/polis-postgres:dev "docker-entrypoint.s…" 12 days ago Up 9 minutes 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp polis-dev-postgres-1

8dccf50c2348 compdem/polis-file-server:dev "docker-entrypoint.s…" 12 days ago Up 8 minutes 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp polis-dev-file-server-1

code::

$ psql -h localhost -p 5432

Password for user ubuntu:

code:example.env

# Optional DB replica for reads:

READ_ONLY_DATABASE_URL=

POSTGRES_DB=polis-dev

POSTGRES_HOST=postgres:5432

POSTGRES_PASSWORD=oiPorg3Nrz0yqDLE

POSTGRES_PORT=5432

POSTGRES_USER=postgres

code::

$ psql -h localhost -p 5432 -U postgres

Password for user postgres:

psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1), server 13.4)

Type "help" for help.

postgres=#

https://gyazo.com/44d6190cf0820fc1f7739ee89aa32e24

code::

\c polis-dev

\dt

https://gyazo.com/5e53dd1760fb0812b08bcbc29ef95f33

code::

# select * from comments where zid = 3;

https://gyazo.com/eba7fc112842308ec172d49585623f13

code::

polis-dev=# COPY comments TO 'comments.csv' DELIMITER ',' CSV HEADER;

ERROR: relative path not allowed for COPY to file

polis-dev=# COPY comments TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;

ERROR: could not open file "/home/ubuntu/comments.csv" for writing: No such file or directory

HINT: COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.

Oh, right, because it's running inside Docker.

code::

# \COPY comments TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;

COPY 55

It's done.

code::

polis-dev=# \COPY (SELECT * FROM comments WHERE zid = 3) TO '/home/ubuntu/comments.csv' DELIMITER ',' CSV HEADER;

COPY 8

polis-dev=# \COPY (SELECT * FROM votes WHERE zid = 3) TO '/home/ubuntu/votes.csv' DELIMITER ',' CSV HEADER;

COPY 454

I've never used Postgres before, but with GPT-4 I'm not afraid of anything (see below).

---